import xlrd
import xlwt
from datetime import date, datetime


def read_excel():
    # 打开excel文件
    workbook = xlrd.open_workbook(r'D:\Python\PythonWork\MyWork\myxlrd_xlwt\demo.xlsx')
    # 获取sheet
    print(workbook.sheet_names())
    sheet2_name = workbook.sheet_names()[1]

    # 根据sheet索引或者名称获取sheet内容
    sheet2_index = workbook.sheet_by_index(1)  # 索引从0开始
    sheet2 = workbook.sheet_by_name('Sheet2')  # 名称区分大小写

    # 打印sheet的名称，行数，列数
    print('名称：{}\n行数:{}\n列数:{}'.format(sheet2_index.name, sheet2_index.nrows, sheet2_index.ncols))
    print('名称：{}\n行数:{}\n列数:{}'.format(sheet2_name, sheet2.nrows, sheet2.ncols))

    # 获取整行和整列的值
    rows = sheet2.row_values(3)  # 获取第四行的值
    cols = sheet2.col_values(2)  # 获取第3列的值
    print('第四行的值为：{}\n第三列的值为：{}'.format(rows, cols))

    # 获取单元格内容
    print('单元格第2行1列内容为：', sheet2.cell(1, 0).value)
    print('单元格第2行1列内容为：', sheet2.cell_value(1, 0))
    print(sheet2.row(1)[0].value)

    # 获取单元格数据类型
    print(sheet2.cell(1, 0).ctype)

    print(sheet2.cell(2, 2).ctype)  # 1991-11-12
    print(sheet2.cell(2, 1).ctype)  # 24
    print(sheet2.cell(2, 0).ctype)  # 小胖
    print(sheet2.cell(2, 4).ctype)  # 因合并单格，为空值
    print(sheet2.cell(2, 2).value)  # 33656.0

    for i in range(sheet2.nrows):
        col_date = sheet2.col_values(2)[i]
        print(col_date)

    date_value = xlrd.xldate_as_tuple(sheet2.cell_value(2, 2), workbook.datemode)
    print(date_value)
    print(date(*date_value[:3]))
    print(date(*date_value[:3]).strftime('%Y/%m/%d'))

if __name__ == '__main__':
    read_excel()
